#coding=utf8
from sqlite3 import connect

class DB:
    def __init__(self, path):
        self.path = path
        self.conn = connect(self.path)
        self.cursor = self.conn.cursor()
    
    def queryAll(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    
    def queryOne(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()
    
    def execute(self, sql, param=None):
        if param == None:
            result = self.cursor.execute(sql)
        else:
            result = self.cursor.execute(sql, param)
        self.cursor.commit()
        return result

    def executeMany(self, sql, param=None):
        if param == None:
            result = self.cursor.execute(sql)
        else:
            result = self.cursor.executemany(sql, param)
        self.cursor.commit()
        return result
    
    def close(self):
        self.conn.close()
class Main:
    
    def __init__(self):
        self.file = open('dball.js', 'w')
        self.db = DB('d:\\Project\\AVPig\\avpig.db')
        self.chapter()
        self.question()
        self.db.close()
        self.file.close()

    def chapter(self):
        cTable = self.db.queryAll('select title, chapter_name from chapter')
        chapter = 'var chapter={\n'
        cq = 'var cq={\n'
        for row in cTable:
            chapter += '\'' + row[1] + '\':\'' + row[0] + '\',\n'
            if row[1] != '0':
                cqTable = self.db.queryOne('select min(_id), count(_id) from question where chapter_name like \'' + row[1] + '%\'')
            else:
                cqTable = self.db.queryOne('select min(_id), count(_id) from question where area=0')
            cq += '\'' + row[1].encode('utf-8') + '\':[' + str(cqTable[0]) + ',' + str(cqTable[1]) + '],\n'
        chapter += '}\n'
        cq += '}\n'
        self.file.write(chapter)
        self.file.write(cq)
    
    def question(self):
        qTable = self.db.queryAll('select _id, content, answer, selectable, chapter_name, pic_name from question')
        question = 'var question={\n' 
        for row in qTable:
            question += str(row[0]) + ':[\'' + row[1].replace('\n', '<br />') + '\',' + str(row[2]) + ',' + str(row[3]) + ',\'' + row[4] + '\',\'' + row[5] + '\'],\n'
        question += '}\n'
        self.file.write(question)
Main()

